WITH RECURSIVE a AS(
    select xzbm,xzmc,xzjb,parent from t_administrative_hlj
    where xzbm = '230102001001'
    UNION ALL
    select a1.xzbm,a1.xzmc,a1.xzjb,a1.parent from t_administrative_hlj a1 JOIN a a2 on a1.xzbm = a2.parent
    where a1.xzjb >= 1
)
SELECT * from a;

/**
  递归查询
    WITH RECURSIVE 标准写法
    a 临时表名
    select xzbm,xzmc,xzjb,parent from t_administrative_hlj where xzbm = '230102001001' 基础数据
    UNION ALL 关联查询结果
    select a1.xzbm,a1.xzmc,a1.xzjb,a1.parent from t_administrative_hlj a1 JOIN a a2 on a1.xzbm = a2.parent 递归查询主体
    a1 本次查询
    a2 上次查询结果
    a1.parent = a2.xzbm 向下查询
    a1.xzbm = a2.parent 向上查询
    在递归查询中，选择 t_administrative_hlj 表中的记录，连接到前一级的递归结果（a），并通过条件 on a1.xzbm = a2.parent 进行连接。这样可以构建一个向上层级递归的结构
    where a1.xzjb >= 1 递归结束条件
 */




WITH RECURSIVE a AS(
    select xzbm,xzmc,xzjb,parent from t_administrative_hlj
    where xzbm = '230100000000'
    UNION ALL
    select a1.xzbm,a1.xzmc,a1.xzjb,a1.parent from t_administrative_hlj a1
                                                      JOIN a a2 on a1.parent = a2.xzbm
    where a1.xzjb >= 1
)
SELECT * from a;



/* oracle递归查询子集 */
SELECT REGION_ID
FROM CON_REGION
         START WITH PARENT_REGION_ID = #{bo.regionId}
CONNECT BY PRIOR REGION_ID = PARENT_REGION_ID